package com.softeem.dao.impl;

import com.alibaba.druid.util.StringUtils;
import com.softeem.dao.StudentDao;
import com.softeem.entity.Course;
import com.softeem.entity.Student;
import com.softeem.utils.BaseDao;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class StudentDaoImpl implements StudentDao {

    private QueryRunner qr = new QueryRunner(BaseDao.getDs());

    @Override
    public Integer login(String username, String password) throws SQLException {
        String sql = "select count(id) from admin where id = ? and password = ?";
        Object query = qr.query(sql, new ScalarHandler<>(), username, password);
        return Integer.valueOf(query.toString());
    }

    @Override
    public List<Student> findAll() throws SQLException {
        String sql = "select * from student limit 0,5";
        return qr.query(sql,new BeanListHandler<>(Student.class));
    }


    public Integer total(Student student) throws SQLException {

        //总页数
        String sql = " select ceil(count(stuID)/5) from student where 1=1 ";

        List list = new ArrayList();

        if (!StringUtils.isEmpty(student.getName())){
            sql += " and name like = ?";
            list.add("%" + student.getName() + "%");
        }

        if (!StringUtils.isEmpty(student.getMale())){
            sql += "and male =?";
            list.add(student.getMale());
        }


        Object query = qr.query(sql, new ScalarHandler<>(),list.toArray());

        return Integer.valueOf(query.toString());
    }

    public List<Student> findPages(String pageNo) throws SQLException {
        //根据页码进行分页查寻
        String sql = "select * from student limit ?,5";
        //pageNo*5-5
        int page = Integer.parseInt(pageNo) * 5 - 5;

        return qr.query(sql, new BeanListHandler<>(Student.class), page);
    }

    @Override
    public int addStu(Student stu) throws SQLException {
        System.out.printf(stu.toString());
        String sql = "insert into student(stuID,name,male,age,grade,state) values(?,?,?,?,?,?)";
        return qr.update(sql,stu.getStuID(),stu.getName(),stu.getMale(),stu.getAge(),stu.getGrade(),stu.getState());
    }

    @Override
    public int updateStu(Student stu) throws SQLException {
        String sql = "update student set name=?,male=?,age=?,grade=?,state=? where stuID="+stu.getStuID();
        return qr.update(sql,stu.getName(),stu.getMale(),stu.getAge(),stu.getGrade(),stu.getState());

    }

    @Override
    public Student findOne(String stuid) throws SQLException {
        String sql = "select * from student where stuID="+stuid;
        return qr.query(sql,new BeanHandler<>(Student.class));
    }

    @Override
    public Integer delete(String stuid) throws SQLException {
        String sql = "delete from student where stuID="+stuid;
        return qr.update(sql);
    }

//    @Override
//    public List<Student> findByCase(Student student) throws SQLException {
//        String sql = "select * from student where 1=1 and name like %?% and male = ?";
//        return qr.query(sql,new BeanListHandler<>(Student.class),student.getName(),student.getMale());
//    }

    @Override
    public List<Student> findByCase(Student stu) throws SQLException {
        //"select * from customer"

//        List list = new ArrayList();
//
//        String sql = "select * from student where 1=1 ";
//
//
//        //条件判断
//        if(!StringUtils.isEmpty(stu.getName())) {
//            sql += " and name like ?";
//            list.add("%" + stu.getName() + "%");
//        }
//
//        if(!StringUtils.isEmpty(stu.getMale()) && !stu.getMale().equals("请选择")) {
//            sql += " and male=?";
//            list.add(stu.getMale());
//        }
//
//        sql += "limit ?,5";
//
//        Integer page = Integer.valueOf(pageNo) * 5 - 5;
//
//        list.add(page);
//
//        return qr.query(sql, new BeanListHandler<>(Student.class), list.toArray());

        List list = new ArrayList();

        String sql = "select * from student where 1=1 ";


        //条件判断
        if(!StringUtils.isEmpty(stu.getName())) {
            sql += " and name like ?";
            list.add("%" + stu.getName() + "%");
        }

        if(!StringUtils.isEmpty(stu.getMale()) && !stu.getMale().equals("请选择")) {
            sql += " and male=?";
            list.add(stu.getMale());
        }
        List<Student> list2 = qr.query(sql, new BeanListHandler<>(Student.class), list.toArray());
        System.out.println(list2);
        return list2;
    }

    @Override
    public List<Course> findCourse() throws SQLException {
        String sql = "select * from course";
        return qr.query(sql,new BeanListHandler<>(Course.class));
    }

    @Override
    public int addCourse(Course cou) throws SQLException {
        System.out.printf(cou.toString());
        String sql = "insert into course(cname,cteacher,synopsis) values(?,?,?)";
        return qr.update(sql,cou.getCname(),cou.getCteacher(),cou.getSynopsis());
    }

    @Override
    public int addEle(List<Course> list1, String fileName) throws SQLException {
        String sql = "update course set filename = ? where cno = ?";
//        return qr.update(sql,new BeanListHandler<>(Course.class),fileName,list1.toArray());
        return 0;
    }

    @Override
    public void save(List list) throws SQLException {
        String sql = "insert into teacher values(null,?,?)";
        int update = qr.update(sql,list.toArray());
        System.out.println(update);
    }

    @Override
    public Course findCouByCno(String cno) throws SQLException {
        String sql = "select * from course where cno ="+cno;
        return qr.query(sql,new BeanHandler<>(Course.class));
    }

    @Override
    public Integer updateCou(Course cou) throws SQLException {
        String sql = "update course set cname=?,cteacher=?,synopsis=? where cno="+cou.getCno();
        return qr.update(sql,cou.getCname(),cou.getCteacher(),cou.getSynopsis());
    }

    @Override
    public void addFile(String[] split) throws SQLException {
        String sql = "insert into course(cno,cname,cteacher,synopsis) values (?,?,?,?)";
        //System.out.println(split.toString());
        int i = qr.update(sql, split[0],split[1],split[2],split[3]);
        //int i = qr.update(sql, split.toString());
        //System.out.println("=====" + i);

    }
}
